03. JPA Querying Entities

JPQL is a powerful query language that allows you to define database queries based on your entity model. Its structure and syntax are very similar to SQL. But there is an important difference that I want to point out before I walk you through the different parts of a JPQL query.

JPQL uses the entity object model instead of database tables to define a query. That makes it very comfortable for us Java developers, but you have to keep in mind that the database still uses SQL. Hibernate, or any other JPA implementation, has to transform the JPQL query into SQL.

The FROM clause

The FROM clause defines from which entities the data gets selected. Hibernate, or any other JPA implementation, maps the entities to the according database tables. The syntax of a JPQL FROM clause is similar to SQL but uses the entity model instead of table or column names.

The following code snippet shows a simple JPQL query in which I select all Author entities.

SELECT a FROM Author a

As you can see, I reference the Author entity instead of the author table and assign the identification variable a to it. The identification variable is often called alias and is similar to a variable in your Java code. It is used in all other parts of the query to reference this entity.

The WHERE clause

The next important part of a JPQL query is the WHERE clause which you can use to restrict the selected entities to the ones you need for your use case. The syntax is very similar to SQL, but JPQL supports only a small subset of the SQL features. If you need more sophisticated features for your query, you can use a native SQL query.

JPQL supports a set of basic operators to define comparison expressions. Most of them are identical to the comparison operators supported by SQL, and you can combine them with the logical operators AND, OR and NOT into more complex expressions.

Operators for single-valued expressions:
Equal: author.id = 10
Not equal: author.id <> 10
Greater than: author.id > 10
Greater or equal then: author.id => 10
Smaller than: author.id < 10
Smaller or equal then: author.id <= 10
Between: author.id BETWEEN 5 and 10
Like: author.firstName LIKE ‘%and%’
The % character represents any character sequence. This example restricts the query result to all Authors with a firstName that contains the String ‘and’, like Alexander or Sandra. You can use an _ instead of % as a single character wildcard. You can also negate the operator with NOT to exclude all Authors with a matching firstName.
Is null: author.firstName IS NULL
You can negate the operator with NOT to restrict the query result to all Authors who’s firstName IS NOT NULL.
In: author.firstName IN (‘John’, ‘Jane’)
Restricts the query result to all Authors with the first name John or Jane.

Operators for collection expressions:
Is empty: author.books IS EMPTY
Restricts the query result to all Authors that don’t have any associated Book entities. You can negate the operator (IS NOT EMPTY) to restrict the query result to all Authors with associated Book entities.
Size: size(author.books) > 2
Restricts the query result to all Authors who are associated with more than 2 Book entities.
Member of: :myBook member of author.books
Restricts the query result to all Authors who are associated with a specific Book entity.
You can use one or more of the operators to restrict your query result. The following query returns all Author entities with a firstName attribute that contains the String “and” and an id attribute greater or equal 20 and who have written at least 5 books.

SELECT a FROM Author a WHERE a.firstName like ‘%and%’ and a.id >= 20 and size(author.books) >= 5

The SELECT clause

The projection of your query defines which information you want to retrieve from the database. This part of the query is very different from SQL. In SQL, you specify a set of database columns and functions as your projection. You can do the same in JPQL by selecting a set of entity attributes or functions as scalar values, but you can also define entities or constructor calls as your projection. Hibernate, or any other JPA implementation, maps this information to a set of database columns and function calls to define the projection of the generated SQL statement.

Let’s have a look at the different options. You can use any combination of them in your queries.
Entities
Entities are the most common projection in JPQL queries. Hibernate uses the mapping information of the selected entities to determine the database columns it has to retrieve from the database. It then maps each row of the result set to the selected entities.

SELECT a FROM Author a

It’s comfortable to use entities as your projection. But you should always keep in mind that all entities are managed by the persistence context which creates overhead for read-only use cases. In these situations, it’s better to use scalar values or a constructor reference as a projection.

Scalar values

Scalar value projections are very similar to the projections you know from SQL. Instead of database columns, you select one or more entity attributes or the return value of a function call with your query.

SELECT a.firstName, a.lastName FROM Author a

Constructor references

Constructor references are a good projection for read-only use cases. They’re more comfortable to use than scalar value projections and avoid the overhead of managed entities.

JPQL allows you to define a constructor call in the SELECT clause. You can see an example of it in the following code snippet. You just need to provide the fully qualified class name and specify the constructor parameters of an existing constructor. Similar to the entity projection, Hibernate generates a SQL query which returns the required database columns and uses the constructor reference to instantiate a new object for each record in the result set.

SELECT new org.thoughts.on.java.model.AuthorValue(a.id, a.firstName, a.lastName) FROM Author a

##### Distinct query results
You probably know SQL’s DISTINCT operator which removes duplicates from a projection. JPQL supports this operator as well.

SELECT DISTINCT a.lastName FROM Author a

Query q = em.createQuery(‘SELECT Order o from Order’);

Navigating Relationships in JPQL

Inner Joins

If you want to select data from more than one entity, e.g., all authors and the books they’ve written, you have to join the entities in the FROM clause. The easiest way to do that is to use the defined associations of an entity like in the following code snippet.

SELECT a, b FROM Author a JOIN a.books b

The definition of the Author entity provides all information Hibernate needs to join it to the Book entity, and you don’t have to provide an additional ON statement. In this example, Hibernate uses the primary keys of the Author and Book entity to join them via the association table of the many-to-many association.

JOINs of unrelated entities are not supported by the JPA specification, but you can use a theta join which creates a cartesian product and restricts it in the WHERE clause to the records with matching foreign and primary keys.

SELECT b, p FROM Book b, Publisher p WHERE b.fk_publisher = p.id

Left Outer Joins

INNER JOINs, like the one in the previous example, require that the selected entities fulfill the join condition. The query returned only the Author entities with associated Book entities but not the ones for which the database doesn’t contain a Book entity. If you want to include the authors without published books, you have to use a LEFT JOIN, like in the following code snippet.

SELECT a, b FROM Author a LEFT JOIN a.books b

Additional Join Conditions

The previous examples use the defined association to join the entities. But sometimes you only want to join the related entities which fulfill additional conditions. Since JPA 2.1, you can do this for INNER JOINs, and LEFT JOINs with an additional ON statement.

SELECT a, p FROM Author a JOIN a.publications p ON p.publishingDate > ?1

Path expressions or implicit joins

Path expressions create implicit joins and are one of the benefits provided by the entity model. You can use the ‘.’ operator to navigate to related entities as I do in the following code snippet.

SELECT b FROM Book b WHERE b.publisher.name LIKE ‘%es%

As you can see, I use the ‘.’ operator to navigate via the publisher attribute of the Book entity b to the related Publisher entities. That creates an implicit join between the Book and Publisher entity which will be translated into an additional join statement in the SQL query.